﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Web.Mvc;

namespace MvcSite.Models
{
    public class GestoreAlbum : GestoreBase
    {
        public static List<Album> ListAlbum()
        {
            List<Album> album = new List<Album>();
            SqlConnection objConn = new SqlConnection(stringConnection);
            objConn.Open();
            string query = "SELECT * FROM Album";
            SqlCommand objCmd = new SqlCommand(query, objConn);
            SqlDataReader reader = objCmd.ExecuteReader();
            while (reader.Read())
                album.Add(new Album(int.Parse(reader["id"].ToString()), reader["nome"].ToString(), int.Parse(reader["n_foto"].ToString())));
            return album;
        }

        public static List<SelectListItem> ListAlbum(bool lista)
        {
            List<SelectListItem> album = new List<SelectListItem>();
            SelectListItem item;
            bool first = true;

            using (SqlConnection objConn = new SqlConnection(stringConnection))
            {
                objConn.Open();
                string query = "SELECT * FROM Album WHERE n_foto > 0";
                SqlCommand objCmd = new SqlCommand(query, objConn);
                using (SqlDataReader reader = objCmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        item = new SelectListItem { Value = reader["id"].ToString(), Text = reader["nome"].ToString() };
                        item.Selected = first;
                        if (first)
                        {
                            first = false;
                        }
                        album.Add(item);
                    }
                }
            }
            return album;
        }


        public static bool AlbumExist(string nome)
        {
            bool exist = false;
            MotoreDataBase.GestoreBase.stringConnection = stringConnection;
            int n = MotoreDataBase.GestoreBase.ExecuteNonQueryToNRow<Album>("SelectFromName", new MotoreDataBase.Parameter("nome_album", nome));
            if (n > 0)
                exist = true;
            //using (SqlConnection objConn = new SqlConnection(stringConnection))
            //{
            //    objConn.Open();
            //    string query = "SELECT nome FROM Album WHERE nome = @nome_album";
            //    SqlCommand objCmd = new SqlCommand(query, objConn);
            //    objCmd.Parameters.AddWithValue("nome_album", nome);
            //    exist = objCmd.ExecuteReader().Read();
            //}
            return exist;
        }

        public static void RenameAlbum(int id, string newName)
        {
            using (SqlConnection objConn = new SqlConnection(stringConnection))
            {
                objConn.Open();
                string query = "UPDATE Album SET nome = @newName WHERE id = @id";
                SqlCommand objCmd = new SqlCommand(query, objConn);
                objCmd.Parameters.Add("newName", newName);
                objCmd.Parameters.Add("id", id);
                if (objCmd.ExecuteNonQuery() != 1)
                    throw new Exception("Errore update nome album");
            }
        }

        public static void DeleteAlbum(int categoria)
        {
            if(MotoreDataBase.GestoreBase.DeleteFromId<Album>(categoria))
            {
                if (System.IO.Directory.Exists(GetPathFromServer(categoria.ToString())))
                {
                    System.IO.Directory.Delete(GetPathFromServer(categoria.ToString()), true);
                }
            }
            GestoreFoto.DeleteFoto(categoria);            
        }

        public static Album CreateAlbum(string Nome)
        {
            Album album = new Album();
            MotoreDataBase.GestoreBase.stringConnection = stringConnection;
            album = MotoreDataBase.GestoreBase.InsertWithId<Album>(new Album(Nome, 0));
            
            //using (SqlConnection objConn = new SqlConnection(stringConnection))
            //{
            //    objConn.Open();
            //    string query = "INSERT INTO Album (nome) VALUES (@nome_album)";
            //    SqlCommand objCmd = new SqlCommand(query, objConn);
            //    objCmd.Parameters.AddWithValue("nome_album", Nome);
            //    if (objCmd.ExecuteNonQuery() == 1)
            //        album = new Album(Nome, 0);
            //    else
            //        throw new Exception("Errore creazione album");
            //}

            return album;
        }

        public static int CountFoto(string Nome)
        {
            using (SqlConnection objConn = new SqlConnection(stringConnection))
            {
                objConn.Open();
                string query = "SELECT id_img FROM FOTO WHERE album = @nome_album";
                SqlCommand objCmd = new SqlCommand(query, objConn);
                objCmd.Parameters.AddWithValue("nome_album", Nome);
                int n_foto = objCmd.ExecuteNonQuery();
                return n_foto;
            }
        }

        public static void IncrementNFoto(int id)
        {
            using (SqlConnection objConn = new SqlConnection(stringConnection))
            {
                objConn.Open();
                string query = "UPDATE Album SET n_foto = n_foto + 1 WHERE id = @id";
                SqlCommand objCmd = new SqlCommand(query, objConn);
                objCmd.Parameters.AddWithValue("id", id);
                int n_foto = objCmd.ExecuteNonQuery();
            }
        }

        public static void IncrementNFoto(int id, int value)
        {
            using (SqlConnection objConn = new SqlConnection(stringConnection))
            {
                objConn.Open();
                string query = "UPDATE Album SET n_foto = n_foto - @deinc WHERE id = @id";
                SqlCommand objCmd = new SqlCommand(query, objConn);
                objCmd.Parameters.AddWithValue("id", id);
                objCmd.Parameters.AddWithValue("deinc", value);
                int n_foto = objCmd.ExecuteNonQuery();
            }
        }


        public static Album GetAlbum(int id)
        {
            Album album = new Album();
            using (SqlConnection objConn = new SqlConnection(stringConnection))
            {
                objConn.Open();
                string query = "SELECT * FROM Album WHERE id = @id";
                SqlCommand objCmd = new SqlCommand(query, objConn);
                objCmd.Parameters.AddWithValue("id", id);
                using (SqlDataReader reader = objCmd.ExecuteReader())
                {
                    while (reader.Read())
                        album = new Album(reader);
                }
            }
            return album;
        }
    }
}